package DB;

import Model.*;

import java.sql.*;
import java.util.ArrayList;



public class DBPostnrby 
{
	private Connection con;
	
	private static DBPostnrby   instance = null;
    
    public static DBPostnrby getInstance()
    {
        if (instance == null)
        {
          instance = new DBPostnrby();
        }
        return instance;
    }
	
	public DBPostnrby()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	public ArrayList getAllPostnrby(boolean retrieveAssociation)
	{ 
		return miscWhere("", retrieveAssociation);
	}
    
    public Postnrby findPostnrby(int Postnr, boolean retrieveAssociation) 
    {
    	String wClause = "  postnr = " + Integer.toString(Postnr) + "";
    	return singleWhere(wClause, retrieveAssociation);
    }
    
    public Postnrby findPostnr(int Postnr, String by, boolean retrieveAssociation) 
    {
    	String wClause = "  postnr = " + Integer.toString(Postnr) + " AND Bynavn LIKE '%"+by+"%'";
    	return singleWhere(wClause, retrieveAssociation);
    }
    
    public Postnrby findPostnrbyId(int Postnr, boolean retriveAssociation) 
    {
    	String wClause = "  postnrbyid = " + Integer.toString(Postnr) + "";
    	return singleWhere(wClause, retriveAssociation);
    }
    
    private void create(String wClause)
	{
	    String query="INSERT INTO Postnrby ";
	   
		
		if (wClause.length()>0)
		{
			query=query+ wClause;
			
			Statement stmt;
			String sql;     
			int rows;
			
			try
			{
				stmt = con.createStatement(); 
				rows = stmt.executeUpdate(query); 
				con.commit(); 
				stmt.close();
			}
			catch (Exception e)
			{ 
				e.printStackTrace(); 
			}
		}
		else
		{
		}
	}
    
    public void createpostnr(Postnrby postnr) 
    {  
    	String wClause = "VALUES " 
	        + "(" + postnr.getPostnr()  + ","
	        + "'" + postnr.getBy() + "')";
	        create(wClause);
    }
    
	public void updatePostnrby(int PostnrbyId, Postnrby p)
	{
		
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		   ("UPDATE Postnrby SET " 
				+ "Postnr = '" + p.getPostnr() + "'"
		   		+ "By = '" + p.getBy() + "'"
		   	+ " WHERE PostnrbyId = " + PostnrbyId + "");
		  prepstmt.executeUpdate();
		             
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	}
    
	public void deletePostnr(int PostnrbyId)
	{
		PreparedStatement prepstmt;
		try 
		{     
		  prepstmt = con.prepareStatement
		    ("DELETE FROM Postnrby WHERE PostnrbyId = " + PostnrbyId + "");   
		  prepstmt.executeUpdate();          
		  con.commit();
		  prepstmt.close();
		}
		catch (Exception e) 
		{
		  e.printStackTrace();
		}
	} 
	
    
    private ArrayList<Person> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
				 Postnrby postnrbyObj = new Postnrby();
				 postnrbyObj = buildPostnrby(results);	
                 list.add(postnrbyObj);	
			}
			stmt.close();
		}
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private Postnrby singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Postnrby postnrbyObj = new Postnrby();
	    String query =  buildQuery(wClause);
		try{ // read the zipCity from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 				postnrbyObj = buildPostnrby(results);
                            stmt.close();	                            
			}
		
		}
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return postnrbyObj;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Postnrby";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private Postnrby buildPostnrby(ResultSet results)
    {   
		Postnrby postnrbyObj = new Postnrby();
        try
        {
        	postnrbyObj.setPostnrbyid(results.getInt(1));
        	postnrbyObj.setPostnr(results.getInt(2));
        	postnrbyObj.setBy(results.getString(3));
        }
        catch(Exception e)
       {
           System.out.println("error in building the zipCity object");
       }
       return postnrbyObj;
    }
	
}
